// .........................................................................
// Title: nonus_panel.do
//
// Imports data on prices and characteristics for bonds in the non-US sample;
// assembles non-US panel for analysis
// .........................................................................

* baseline panel with characteristics
use "$tmp/amounts_outstanding_y", clear
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m)
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation.dta", unmatched(m)
mmerge issuer_number using "$output/cgs/cgs_ai_aggregation.dta", unmatched(m) umatch(cusip6)
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m)
drop *source*
drop _merge
save "$tmp/nonus_panel_prep_step1", replace

* import prices: covid event
import delimited using "$raw/ciq/CIQ-Prices-COVID.csv", clear varnames(1) case(lower)
foreach var of varlist price* {
    replace `var' = "" if `var' == "(Invalid Identifier)"
    destring `var', replace
    replace `var' = . if `var' == 0
}
save "$tmp/ciq_prices_raw_covid", replace

* import prices: great recession
import delimited using "$raw/ciq/CIQ-Prices-GR.csv", clear varnames(1) case(lower)
foreach var of varlist price* {
    replace `var' = "" if `var' == "(Invalid Identifier)"
    destring `var', replace
    replace `var' = . if `var' == 0
}
save "$tmp/ciq_prices_raw_gr", replace

* import prices: p16 event
import delimited using "$raw/ciq/CIQ-Prices-P16.csv", clear varnames(1) case(lower)
foreach var of varlist price* {
    replace `var' = "" if `var' == "(Invalid Identifier)"
    destring `var', replace
    replace `var' = . if `var' == 0
}
save "$tmp/ciq_prices_raw_p16", replace

* import prices: p11 event
import delimited using "$raw/ciq/CIQ-Prices-P11.csv", clear varnames(1) case(lower)
foreach var of varlist price* {
    replace `var' = "" if `var' == "(Invalid Identifier)"
    destring `var', replace
    replace `var' = . if `var' == 0
}
save "$tmp/ciq_prices_raw_p11", replace

* reshape prices to long format
foreach event in "gr" "p11" "p16" "covid" {
    di "Processing `event'"
    use "$tmp/ciq_prices_raw_`event'", clear
    reshape long price_, i(cusip isin) j(_date) string
    gen month = substr(_date, 1, 2)
    gen day = substr(_date, 3, 2)
    gen year = substr(_date, 5, 4)
    destring day month year, replace
    gen date = mdy(month, day, year)
    format %td date
    drop month year day
    drop _date
    rename price_ price
    gen date_w = wofd(date)
    format %tw date_w
    sort cusip date_w
    gen event = "`event'"
    order cusip isin event date_w date price
    save "$tmp/ciq_prices_long_`event'", replace
}

* append the events
clear
foreach event in "gr" "p11" "p16" "covid" {
    append using "$tmp/ciq_prices_long_`event'"
}
save "$tmp/ciq_prices_long", replace

* add bond durations
use "$tmp/ciq_prices_long", clear
gen date_m = mofd(date)
gen year = year(date)
format %tm date_m
mmerge cusip date_m using "$tmp/duration_merged", unmatched(m)
drop if duration_m < 0
replace duration_m = 360 if duration_m > 360
save "$tmp/nonus_drawdown_prep_step1", replace

* collapse to weekly
use "$tmp/nonus_drawdown_prep_step1", clear
gsort cusip date_w date
collapse (lastnm) price duration_m, by(cusip event date_w)
egen _cusip_event = group(cusip event)
gsort _cusip_event date_w
xtset _cusip_event date_w
tsfill

* merge coupon details 
mmerge cusip using "$tmp/interest_details_consolidated", unmatched(m)
drop _merge
gsort _cusip_event date_w
by _cusip_event: gen prev_date_w = date_w[_n-1]
gen first_interest_date_w = wofd(first_interest_date)
gen maturity_date_w = wofd(maturity_date)
format %tw prev_date_w first_interest_date_w maturity_date_w

* interest frequency to number of coupons per year
gen ncoups = interest_frequency
replace ncoups = 0 if ncoups < 0 | ncoups > 12 // variable frequencies

* number of weeks since first interest date
gen coup_weeks = date_w - first_interest_date_w

* calculate next coupon date
gen nextcoup = first_interest_date_w if date_w <= first_interest_date_w
gen coup_gap = (1 + floor(coup_weeks * ncoups / 48)) * 48 / ncoups
replace nextcoup = first_interest_date_w + coup_gap if date_w > first_interest_date_w & maturity_date_w >= date_w & ncoups > 0
replace nextcoup = maturity_date_w if nextcoup > 0 & maturity_date_w < nextcoup
format %tw nextcoup

* coupon amount, accrued interest
gen coup_amt = coupon / ncoups * (mod(coup_weeks, 48 / ncoups) == 0) if coup_weeks >= 0 & ncoups > 0 & date_w >= first_interest_date_w
gen prevcoup = nextcoup - 48 / ncoups
replace prevcoup = first_interest_date_w if prevcoup < first_interest_date_w
gen coup_acc = coupon * (date_w - prevcoup) * 7 / 365 if coup_weeks >= 0 & ncoups > 0 & date_w >= first_interest_date_w
replace coup_acc = 0 if missing(coup_acc)
replace coup_amt = 0 if missing(coup_amt)
replace coup_amt = 0 if date_w > maturity_date_w | coup_amt < 0
replace coup_acc = 0 if date_w > maturity_date_w | coup_acc < 0

* multi-coupon corner cases
gen weeks_gap = date_w - prev_date_w
replace weeks_gap = 0 if missing(weeks_gap)
gen multi_coups = 1 + floor((weeks_gap - 1) * ncoups / 48) if weeks_gap > 1
replace multi_coups = 1 if missing(multi_coups)

* lagged values
gsort _cusip_event date_w
by _cusip_event: gen prev_coup_acc = coup_acc[_n-1]
by _cusip_event: gen prev_price = price[_n-1]

* returns
gen net_return = (price + coup_acc + coup_amt * multi_coups) / (prev_price + prev_coup_acc) - 1 if ~missing(prev_price) & prev_price > 0
replace net_return = . if net_return > 1 & ~missing(net_return) // don't use implausibly large values
drop _cusip
drop weeks_gap multi_coups coup_amt coup_acc prevcoup
save "$tmp/nonus_drawdown_prep_step2", replace

* subset on relevant countries, merge in sovereigns
use "$tmp/nonus_drawdown_prep_step2", clear
gen issuer_number = substr(cusip, 1, 6)
mmerge issuer_number using "$raw/cmns/cmns_aggregation.dta", unmatched(m) ukeep(cusip6_up_bg country_bg)
replace country_bg = "EMU" if inlist(country_bg, $eu1) | inlist(country_bg, $eu2) | inlist(country_bg, $eu3)
keep if inlist(country_bg, "EMU", "GBR", "CAN")
gen country = country_bg
replace country = "DEU" if country == "EMU"
rename duration_m duration
mmerge country duration date_w using "$tmp/sovereign_returns_nonus_cmb_w", unmatched(m)
drop if missing(sovereign_return)
assert _merge == 3
drop _merge

* do the hedging
winsor2 net_return, cuts(.1 99.9)
gen hedged_return = net_return_w - sovereign_return
cap drop hedged_tri

* hedged return index
gsort cusip event date_w
replace hedged_return = log(1 + hedged_return)
by cusip event: gen hedged_tri = 1 + sum(hedged_return)
replace hedged_tri = exp(hedged_tri) - 1
replace hedged_tri = . if missing(hedged_return)
save "$tmp/nonus_drawdown_prep_step3", replace

* data to compute drawdowns, by event
foreach event in "gr" "p11" "p16" "covid" {
    use "$tmp/nonus_drawdown_prep_step3", clear
    keep if event == "`event'"
    if "`event'" == "gr" {
        drop if date_w < $min_security_window_w | date_w > $max_security_window_w
    }
    if "`event'" == "p11" {
        drop if date_w < $min_security_window_p11_w | date_w > $max_security_window_p11_w
    }
    if "`event'" == "p16" {
        drop if date_w < $min_security_window_p16_w | date_w > $max_security_window_p16_w
    }
    if "`event'" == "covid" {
        drop if date_w < $min_security_window_covid_w | date_w > $max_security_window_covid_w
    }
    keep cusip date_w hedged_tri
    gsort cusip date_w
    save "$tmp/drawdowns_forpy_`event'_nonus_hedged", replace
}

* run python method for drawdowns
foreach event in "gr" "p11" "p16" "covid" {
    shell $python_interpreter_path $code_path/scripts/compute_drawdowns.py ///
        --input_file "$tmp/drawdowns_forpy_`event'_nonus_hedged.dta" ///
        --id_field "cusip" ///
        --date_field "date_w" ///
        --price_field "hedged_tri" ///
        --out_file "$tmp/drawdowns_frompy_`event'_nonus_hedged.dta"
}

* append them
clear
gen event = ""
foreach event in "gr" "p11" "p16" "covid" {
    append using "$tmp/drawdowns_frompy_`event'_nonus_hedged.dta"
    replace event = "`event'" if missing(event)
}
save "$tmp/nonus_all_drawdowns", replace

* prices at event start, for market adjustment
use "$tmp/ciq_prices_long", clear
gsort cusip event date_w date
by cusip event date_w: keep if _n == _N
drop date
egen _cusip = group(cusip event)
gsort _cusip date_w
xtset _cusip date_w
tsfill
by _cusip: carryforward price, replace
cap drop event
gen event = ""
replace event = "gr" if date_w == $min_security_window_w
replace event = "p11" if date_w == $min_security_window_p11_w
replace event = "p16" if date_w == $min_security_window_p16_w
replace event = "covid" if date_w == $min_security_window_covid_w
keep if ~missing(event)
keep cusip event price
order cusip event price
save "$tmp/nonus_event_price_summary", replace

* duration at event start
use "$tmp/nonus_drawdown_prep_step1", clear
gsort cusip event date_w date
by cusip event date_w: keep if _n == _N
drop date
egen _cusip = group(cusip event)
gsort _cusip date_w
xtset _cusip date_w
tsfill
by _cusip: carryforward duration_m duration_y, replace
cap drop event
gen event = ""
replace event = "gr" if date_w == $min_security_window_w
replace event = "p11" if date_w == $min_security_window_p11_w
replace event = "p16" if date_w == $min_security_window_p16_w
replace event = "covid" if date_w == $min_security_window_covid_w
keep if ~missing(event)
keep cusip event duration_m duration_y
order cusip event duration_m duration_y
save "$tmp/nonus_event_duration_summary", replace

* merge baseline panel and drawdowns (selecting rows on ex-ante dates)
use "$tmp/nonus_panel_prep_step1", clear
gen event = ""
replace event = "gr" if year == 2007
replace event = "p11" if year == 2010
replace event = "p16" if year == 2014
replace event = "covid" if year == 2019
drop if missing(event)
mmerge cusip event using "$tmp/nonus_all_drawdowns"
keep if _merge == 3
replace country_bg = "EMU" if inlist(country_bg, $eu1) | inlist(country_bg, $eu2) | inlist(country_bg, $eu3)
assert inlist(country_bg, "EMU", "GBR", "CAN")
rename py_drawdown hedged_drawdown
drop _merge
save "$tmp/nonus_panel_prep_step2", replace

* subset on ig
use "$tmp/nonus_panel_prep_step2", clear
keep if inlist(rating_cat, "AAA", "AA", "A", "BBB")

* amounts at market value
mmerge cusip event using "$tmp/nonus_event_price_summary", unmatched(m)
gen value_outstanding_mv = value_outstanding * price / 100
drop if missing(value_outstanding_mv)

* size quantiles
egen size_decile = xtile(value_outstanding), nq(10)
save "$tmp/nonus_panel_prep_step3", replace

* issuer numbers to moodys issuer id mapping
use "$tmp/moodys/cusip_to_moodys_issuer", clear
gen issuer_number = substr(cusip, 1, 6)
gen count = 1
gcollapse (sum) count, by(issuer_number mast_issr_num)
gsort mast_issr_num -count
by mast_issr_num: keep if _n == 1
mmerge mast_issr_num using "$raw/moodys/stata/master_issuer", unmatched(m) ukeep(issuer_nam naic)
drop _merge
save "$tmp/issuer_num_to_moodys_issuer", replace

* issuer numbers to mergent issuer id
use "$tmp/mergent_issuer_ids", clear
gen issuer_number = substr(cusip, 1, 6)
gen count = 1
collapse (sum) count (firstnm) mergent_issuer_name, by(issuer_number mergent_issuer_id)
gsort mergent_issuer_id -count
by mergent_issuer_id: keep if _n == 1
save "$tmp/issuer_num_to_mergent_issuer", replace

* add in issuer ids
use "$tmp/nonus_panel_prep_step3", clear
mmerge cusip using "$tmp/moodys/cusip_to_moodys_issuer", unmatched(m)
mmerge issuer_number using "$tmp/issuer_num_to_moodys_issuer", unmatched(m) update
mmerge cusip using "$tmp/mergent_issuer_ids", unmatched(m)
mmerge issuer_number using "$tmp/issuer_num_to_mergent_issuer", unmatched(m) update
gen moodys_iid = mast_issr_num
gen mergent_iid = mergent_issuer_id
tostring mergent_iid moodys_iid, replace
replace moodys_iid = "" if missing(mast_issr_num)
replace mergent_iid = "" if missing(mergent_issuer_id)
replace mergent_iid = "ME_" + mergent_iid if ~missing(mergent_iid)
replace moodys_iid = "MO_" + moodys_iid if ~missing(moodys_iid)
save "$tmp/nonus_panel_prep_step4", replace

* consolidate issuer id's
use "$tmp/nonus_panel_prep_step4", clear
gen consolidated_issuer_id = mergent_iid
replace consolidated_issuer_id = ai_cusip6 if missing(consolidated_issuer_id)
replace consolidated_issuer_id = moodys_iid if missing(consolidated_issuer_id)
replace consolidated_issuer_id = issuer_number if missing(consolidated_issuer_id)
assert ~missing(consolidated_issuer_id)

* add other bond characteristics
mmerge cusip using "$tmp/nonus_event_duration_summary", unmatched(m)
mmerge cusip using "$tmp/ciq_static_characteristics_processed", unmatched(m)
mmerge cusip using "$tmp/consolidated_static_dummies", unmatched(m) ukeep(bc_is_senior bc_is_floating bc_is_callable) update
drop _merge
gen_duration_category
rename bc_is_callable callable
rename bc_is_senior senior_bond
rename bc_is_floating floating
cap rename drn_bucket duration_bucket

* encode and save
replace currency = "OTH" if ~inlist(currency, "USD", "EUR", "GBP", "CAD", "CHF", "JPY")
encode consolidated_issuer_id, gen(_firm)
foreach var of varlist duration_bucket rating_cat_pm country_bg event callable senior_bond consolidated_issuer_id currency size_decile floating {
    drop if missing(`var')
}
egen full_fe_category = group(event size_decile duration_bucket rating_cat_pm _firm callable senior_bond floating)
save "$tmp/nonus_panel_prep_step5", replace

* merge in fund holdings
use "$tmp/nonus_panel_prep_step5", clear
gen date_q = .
format %tq date_q
replace date_q = $ex_ante_positions if event == "gr"
replace date_q = $ex_ante_positions_p11 if event == "p11"
replace date_q = $ex_ante_positions_p16 if event == "p16"
replace date_q = $ex_ante_positions_covid if event == "covid"
mmerge cusip date_q using "$tmp/morningstar_summary/mns_cusip_quarter_amounts", unmatched(m)
gen fund_holdings = marketvalue_usd / 1e6

gen funds_share = fund_holdings / value_outstanding_mv
replace funds_share = . if funds_share > $max_holdings_ratio
replace funds_share = . if funds_share < 0
gen nonfund_share = 1 - funds_share
keep if class_code2 == "BC"

* wide currency, clusters
foreach cur in "USD" "EUR" "GBP" "CAD" "CHF" "JPY" "OTH" {
    gen currency_`cur' = (currency == "`cur'")
}
egen firm_event_cluster = group(_firm event)
save "$tmp/nonus_regressions_panel", replace
